import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestJdbc {
    /**
     * 基本使用
     */
    @Test
    public void jdbcHelloWorld() {
        Connection connection = null;
        Statement statement = null;
        //初始化驱动
        try {
            //1. 初始化驱动
            //驱动类 com.mysql.cj.jdbc.Driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
            //2. 建立与数据库的Connection连接
            //这里需要提供：
            //数据库所处于的ip：60.205.142.63
            //数据库的端口号：3306
            //数据库名称：ry-vue
            //账号：root
            //密码：root
            String url = "jdbc:mysql://60.205.142.63:3306/how2j?characterEncoding=UTF-8";
            String username = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, username, password);
            System.out.println("获取连接成功：connection = " + connection);
            //3.创建Statement，Statement是用于执行SQL语句的，比如增加，删除
            //注意：使用的是java.sql.Statement
            //不要不小心使用到：com.mysql.jdbc.Statement
            statement = connection.createStatement();
            System.out.println("statement = " + statement);
            //statement.execute()执行SQL语句
            String sql = "insert into hero(name,hp,damage) values('德玛西亚之力',900,12)";
            boolean execute = statement.execute(sql);
            System.out.println("插入语句 执行成功");

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            //数据库的连接是有限资源，相关操作结束后，要关闭
            //先关闭statement
            //后关闭collection
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    /**
     * 使用try-with-resource的方式自动关闭连接
     */
    @Test
    public void tryWithResource() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (
                Connection connection =
                        DriverManager.getConnection("jdbc:mysql://60.205.142.63:3306/how2j?characterEncoding=UTF-8", "root", "root");
                Statement statement = connection.createStatement()

        ) {
            String sql = "insert into hero(name,hp,damage) values('塞拉斯',700,18)";
            statement.execute(sql);
            System.out.println("插入语句执行成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 测试：插入1000条数据
     */
    @Test
    public void insert1000Data() {
        //1. 加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try (
                Connection connection = DriverManager.getConnection("jdbc:mysql://60.205.142.63:3306/how2j?characterEncoding=UTF-8", "root", "root");
                Statement statement = connection.createStatement();) {
            StringBuilder sql = new StringBuilder("insert into hero (name,hp,damage) values");
            int sum = 0;
            for (int i = 0; i < 1000; i++) {
                sql.append("('hero" + i + "',700+" + i + ",12)");
                sum++;
                if (i != 999) {
                    sql.append(",");
                }
            }
            System.out.println("sum = " + sum);
            System.out.println("sql.toString() = " + sql.toString());
            statement.execute(sql.toString());
            System.out.println("sql执行完毕");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}
